{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<!-- （勿改动，执行即可）执行更改背景 -->\n",
       "<link rel=\"stylesheet\" href=\"exam.css\" type=\"text/css\">\n",
       "<h1 style=\"color: red;\">注意单元格的第一行不能改动，否则会影响自动打分</h1>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "<!-- （勿改动，执行即可）执行更改背景 -->\n",
    "<link rel=\"stylesheet\" href=\"exam.css\" type=\"text/css\">\n",
    "<h1 style=\"color: red;\">注意单元格的第一行不能改动，否则会影响自动打分</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "# Big Data期末考（S卷）：目前工作目录C:\\Users\\nfulab\n",
       "* 共5题，每题20分，40分及格，最高分100，作题时间45分钟。\n",
       "*  答题格首行如 ***# 003*** 勿删除或改动 \n",
       "* 可先挑难度较易的题先做，🌶个数愈高愈难\n",
       "* 执行一格格，最後一格可回报分数（仅供参考）\n",
       " ##提交此.ipynb档，必檢查： \n",
       "   * 档名✍S_学号✍（只能用半角数字9碼）\n",
       "   *  下格 输入学号（半角数字9碼） \n",
       "\n",
       "\n",
       "# 🛂输入学号🛂"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 始000（勿改动，执行即可）\n",
    "e = %env\n",
    "_which_= \"S\"  # 卷號\n",
    "import PandasCourse as PC\n",
    "from IPython.display import Markdown\n",
    "Markdown(PC.msgs['opening'].format(w=_which_, d=e['HOMEDRIVE']+ e['HOMEPATH']))    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 始001（✍請改动並执行）\n",
    "student_id = \"171013095\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "#  🙊🙈🙉 数据读入与清洗 🙉🙈🙊 \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 题一（20 分）🌶 易"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### dataPANDAS目录下有 fsnd_zb_sample_salary.tsv\n",
    "输出一字典dict_reg，其键及其值对映到原数据分别为 \n",
    "* 键：'指标'\n",
    "* 值：'指标名'\n",
    "* 答案提示：{'A040A06': '建筑业城镇单位就业人员平均工资', 'A040A08': '信息传输 ....."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'A040A06': '建筑业城镇单位就业人员平均工资', 'A040A08': '信息传输、计算机服务和软件业城镇单位就业人员平均工资', 'A040A0B': '金融业城镇单位就业人员平均工资', 'A040A0E': '科学研究、技术服务和地质勘查业城镇单位就业人员平均工资', 'A040B06': '建筑业城镇私营单位就业人员平均工资', 'A040B08': '信息传输、计算机服务和软件业城镇私营单位就业人员平均工资', 'A040B0B': '金融业城镇私营单位就业人员平均工资', 'A040B0E': '科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资'}\n"
     ]
    }
   ],
   "source": [
    "# 001\n",
    "import pandas as pd\n",
    "df_all = pd.read_table(\"dataPANDAS/fsnd_zb_sample_salary.tsv\", encoding=\"utf8\", na_values='na_rep')\n",
    "\n",
    "dict_reg = df_all[['指标','指标名']].set_index('指标')['指标名'].to_dict()\n",
    "print (dict_reg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 题二（20 分）🌶 易"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据读入的数据，产出一数据框df\n",
    "* 只选取原数据的栏位数据：['地区', '指标名', '2011', '2013', '2015', '2017']\n",
    "* 也就是移除 '地区', '指标' 两栏位，或这两栏位不选\n",
    "* 此数据框需为新的副本，使用.copy()\n",
    "* 此数据框的索引需要设为['地区', '指标名']\n",
    "* 答案提示（前几行像）：\n",
    "![002](dataPANDAS/002.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2011</th>\n",
       "      <th>2013</th>\n",
       "      <th>2015</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>指标名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">110000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>52455.0</td>\n",
       "      <td>68501.0</td>\n",
       "      <td>82251.0</td>\n",
       "      <td>99718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>116755.0</td>\n",
       "      <td>136599.0</td>\n",
       "      <td>159486.0</td>\n",
       "      <td>183183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>172621.0</td>\n",
       "      <td>206110.0</td>\n",
       "      <td>248320.0</td>\n",
       "      <td>253637.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>97658.0</td>\n",
       "      <td>113206.0</td>\n",
       "      <td>132339.0</td>\n",
       "      <td>150611.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29934.0</td>\n",
       "      <td>40942.0</td>\n",
       "      <td>45909.0</td>\n",
       "      <td>53403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>55164.0</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>90724.0</td>\n",
       "      <td>124409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>36619.0</td>\n",
       "      <td>61099.0</td>\n",
       "      <td>111921.0</td>\n",
       "      <td>144952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>37211.0</td>\n",
       "      <td>55689.0</td>\n",
       "      <td>66597.0</td>\n",
       "      <td>79731.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">120000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>43161.0</td>\n",
       "      <td>54095.0</td>\n",
       "      <td>64141.0</td>\n",
       "      <td>70649.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>74804.0</td>\n",
       "      <td>102922.0</td>\n",
       "      <td>134331.0</td>\n",
       "      <td>151778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>97006.0</td>\n",
       "      <td>118448.0</td>\n",
       "      <td>112059.0</td>\n",
       "      <td>113813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>91848.0</td>\n",
       "      <td>102610.0</td>\n",
       "      <td>123312.0</td>\n",
       "      <td>137969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29177.0</td>\n",
       "      <td>39704.0</td>\n",
       "      <td>46771.0</td>\n",
       "      <td>49933.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>41082.0</td>\n",
       "      <td>46580.0</td>\n",
       "      <td>79928.0</td>\n",
       "      <td>95320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>31071.0</td>\n",
       "      <td>43257.0</td>\n",
       "      <td>57510.0</td>\n",
       "      <td>81116.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29137.0</td>\n",
       "      <td>43931.0</td>\n",
       "      <td>60946.0</td>\n",
       "      <td>69255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">130000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>27425.0</td>\n",
       "      <td>34670.0</td>\n",
       "      <td>39182.0</td>\n",
       "      <td>51771.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>46842.0</td>\n",
       "      <td>69718.0</td>\n",
       "      <td>93983.0</td>\n",
       "      <td>84317.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>53190.0</td>\n",
       "      <td>65547.0</td>\n",
       "      <td>74795.0</td>\n",
       "      <td>77845.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>59318.0</td>\n",
       "      <td>61114.0</td>\n",
       "      <td>69744.0</td>\n",
       "      <td>81947.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22670.0</td>\n",
       "      <td>28852.0</td>\n",
       "      <td>33813.0</td>\n",
       "      <td>38191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18638.0</td>\n",
       "      <td>27827.0</td>\n",
       "      <td>34832.0</td>\n",
       "      <td>42182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21833.0</td>\n",
       "      <td>29054.0</td>\n",
       "      <td>34564.0</td>\n",
       "      <td>41474.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22348.0</td>\n",
       "      <td>31978.0</td>\n",
       "      <td>37431.0</td>\n",
       "      <td>42280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">140000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>30399.0</td>\n",
       "      <td>36719.0</td>\n",
       "      <td>42494.0</td>\n",
       "      <td>50384.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>35697.0</td>\n",
       "      <td>49299.0</td>\n",
       "      <td>60160.0</td>\n",
       "      <td>77870.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>55125.0</td>\n",
       "      <td>67729.0</td>\n",
       "      <td>75620.0</td>\n",
       "      <td>80556.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>37549.0</td>\n",
       "      <td>50793.0</td>\n",
       "      <td>58390.0</td>\n",
       "      <td>67918.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21658.0</td>\n",
       "      <td>29185.0</td>\n",
       "      <td>35486.0</td>\n",
       "      <td>35768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>17068.0</td>\n",
       "      <td>21177.0</td>\n",
       "      <td>33029.0</td>\n",
       "      <td>30677.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">620000</th>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>38163.0</td>\n",
       "      <td>48791.0</td>\n",
       "      <td>59923.0</td>\n",
       "      <td>63050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>34475.0</td>\n",
       "      <td>49957.0</td>\n",
       "      <td>67352.0</td>\n",
       "      <td>85197.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18834.0</td>\n",
       "      <td>25256.0</td>\n",
       "      <td>30687.0</td>\n",
       "      <td>36270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21060.0</td>\n",
       "      <td>25994.0</td>\n",
       "      <td>30890.0</td>\n",
       "      <td>35310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18577.0</td>\n",
       "      <td>21144.0</td>\n",
       "      <td>29668.0</td>\n",
       "      <td>39157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>19531.0</td>\n",
       "      <td>25220.0</td>\n",
       "      <td>35573.0</td>\n",
       "      <td>51435.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">630000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>32352.0</td>\n",
       "      <td>40242.0</td>\n",
       "      <td>48717.0</td>\n",
       "      <td>57674.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>47356.0</td>\n",
       "      <td>49989.0</td>\n",
       "      <td>72527.0</td>\n",
       "      <td>79183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>54446.0</td>\n",
       "      <td>67182.0</td>\n",
       "      <td>81359.0</td>\n",
       "      <td>98911.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>55466.0</td>\n",
       "      <td>59097.0</td>\n",
       "      <td>69678.0</td>\n",
       "      <td>87248.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21598.0</td>\n",
       "      <td>24730.0</td>\n",
       "      <td>36597.0</td>\n",
       "      <td>38588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>17376.0</td>\n",
       "      <td>24681.0</td>\n",
       "      <td>30810.0</td>\n",
       "      <td>35235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18112.0</td>\n",
       "      <td>19408.0</td>\n",
       "      <td>26221.0</td>\n",
       "      <td>28776.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22629.0</td>\n",
       "      <td>40967.0</td>\n",
       "      <td>44716.0</td>\n",
       "      <td>52103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">640000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>29065.0</td>\n",
       "      <td>39589.0</td>\n",
       "      <td>44283.0</td>\n",
       "      <td>47483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>52173.0</td>\n",
       "      <td>65043.0</td>\n",
       "      <td>77487.0</td>\n",
       "      <td>90085.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>62587.0</td>\n",
       "      <td>74026.0</td>\n",
       "      <td>81019.0</td>\n",
       "      <td>85861.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>46198.0</td>\n",
       "      <td>57000.0</td>\n",
       "      <td>71581.0</td>\n",
       "      <td>78182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>23403.0</td>\n",
       "      <td>36178.0</td>\n",
       "      <td>39785.0</td>\n",
       "      <td>42756.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>16305.0</td>\n",
       "      <td>29269.0</td>\n",
       "      <td>32637.0</td>\n",
       "      <td>39889.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25575.0</td>\n",
       "      <td>30540.0</td>\n",
       "      <td>40028.0</td>\n",
       "      <td>38284.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25655.0</td>\n",
       "      <td>38531.0</td>\n",
       "      <td>45598.0</td>\n",
       "      <td>46887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">650000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>35558.0</td>\n",
       "      <td>45940.0</td>\n",
       "      <td>56238.0</td>\n",
       "      <td>59844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>48034.0</td>\n",
       "      <td>64214.0</td>\n",
       "      <td>78238.0</td>\n",
       "      <td>98251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>59732.0</td>\n",
       "      <td>74551.0</td>\n",
       "      <td>88212.0</td>\n",
       "      <td>95374.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>45501.0</td>\n",
       "      <td>60859.0</td>\n",
       "      <td>75478.0</td>\n",
       "      <td>85516.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29929.0</td>\n",
       "      <td>41001.0</td>\n",
       "      <td>45672.0</td>\n",
       "      <td>47699.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>20089.0</td>\n",
       "      <td>31279.0</td>\n",
       "      <td>37964.0</td>\n",
       "      <td>42508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>31007.0</td>\n",
       "      <td>36275.0</td>\n",
       "      <td>44239.0</td>\n",
       "      <td>46858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25645.0</td>\n",
       "      <td>32319.0</td>\n",
       "      <td>43162.0</td>\n",
       "      <td>44347.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>244 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          2011      2013      2015      2017\n",
       "地区     指标名                                                                  \n",
       "110000 建筑业城镇单位就业人员平均工资                 52455.0   68501.0   82251.0   99718.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资     116755.0  136599.0  159486.0  183183.0\n",
       "       金融业城镇单位就业人员平均工资                172621.0  206110.0  248320.0  253637.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     97658.0  113206.0  132339.0  150611.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29934.0   40942.0   45909.0   53403.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    55164.0   73626.0   90724.0  124409.0\n",
       "       金融业城镇私营单位就业人员平均工资               36619.0   61099.0  111921.0  144952.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   37211.0   55689.0   66597.0   79731.0\n",
       "120000 建筑业城镇单位就业人员平均工资                 43161.0   54095.0   64141.0   70649.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      74804.0  102922.0  134331.0  151778.0\n",
       "       金融业城镇单位就业人员平均工资                 97006.0  118448.0  112059.0  113813.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     91848.0  102610.0  123312.0  137969.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29177.0   39704.0   46771.0   49933.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    41082.0   46580.0   79928.0   95320.0\n",
       "       金融业城镇私营单位就业人员平均工资               31071.0   43257.0   57510.0   81116.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   29137.0   43931.0   60946.0   69255.0\n",
       "130000 建筑业城镇单位就业人员平均工资                 27425.0   34670.0   39182.0   51771.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      46842.0   69718.0   93983.0   84317.0\n",
       "       金融业城镇单位就业人员平均工资                 53190.0   65547.0   74795.0   77845.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     59318.0   61114.0   69744.0   81947.0\n",
       "       建筑业城镇私营单位就业人员平均工资               22670.0   28852.0   33813.0   38191.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    18638.0   27827.0   34832.0   42182.0\n",
       "       金融业城镇私营单位就业人员平均工资               21833.0   29054.0   34564.0   41474.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   22348.0   31978.0   37431.0   42280.0\n",
       "140000 建筑业城镇单位就业人员平均工资                 30399.0   36719.0   42494.0   50384.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      35697.0   49299.0   60160.0   77870.0\n",
       "       金融业城镇单位就业人员平均工资                 55125.0   67729.0   75620.0   80556.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     37549.0   50793.0   58390.0   67918.0\n",
       "       建筑业城镇私营单位就业人员平均工资               21658.0   29185.0   35486.0   35768.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    17068.0   21177.0   33029.0   30677.0\n",
       "...                                        ...       ...       ...       ...\n",
       "620000 金融业城镇单位就业人员平均工资                 38163.0   48791.0   59923.0   63050.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     34475.0   49957.0   67352.0   85197.0\n",
       "       建筑业城镇私营单位就业人员平均工资               18834.0   25256.0   30687.0   36270.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    21060.0   25994.0   30890.0   35310.0\n",
       "       金融业城镇私营单位就业人员平均工资               18577.0   21144.0   29668.0   39157.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   19531.0   25220.0   35573.0   51435.0\n",
       "630000 建筑业城镇单位就业人员平均工资                 32352.0   40242.0   48717.0   57674.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      47356.0   49989.0   72527.0   79183.0\n",
       "       金融业城镇单位就业人员平均工资                 54446.0   67182.0   81359.0   98911.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     55466.0   59097.0   69678.0   87248.0\n",
       "       建筑业城镇私营单位就业人员平均工资               21598.0   24730.0   36597.0   38588.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    17376.0   24681.0   30810.0   35235.0\n",
       "       金融业城镇私营单位就业人员平均工资               18112.0   19408.0   26221.0   28776.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   22629.0   40967.0   44716.0   52103.0\n",
       "640000 建筑业城镇单位就业人员平均工资                 29065.0   39589.0   44283.0   47483.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      52173.0   65043.0   77487.0   90085.0\n",
       "       金融业城镇单位就业人员平均工资                 62587.0   74026.0   81019.0   85861.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     46198.0   57000.0   71581.0   78182.0\n",
       "       建筑业城镇私营单位就业人员平均工资               23403.0   36178.0   39785.0   42756.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    16305.0   29269.0   32637.0   39889.0\n",
       "       金融业城镇私营单位就业人员平均工资               25575.0   30540.0   40028.0   38284.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   25655.0   38531.0   45598.0   46887.0\n",
       "650000 建筑业城镇单位就业人员平均工资                 35558.0   45940.0   56238.0   59844.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资      48034.0   64214.0   78238.0   98251.0\n",
       "       金融业城镇单位就业人员平均工资                 59732.0   74551.0   88212.0   95374.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     45501.0   60859.0   75478.0   85516.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29929.0   41001.0   45672.0   47699.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    20089.0   31279.0   37964.0   42508.0\n",
       "       金融业城镇私营单位就业人员平均工资               31007.0   36275.0   44239.0   46858.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   25645.0   32319.0   43162.0   44347.0\n",
       "\n",
       "[244 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 002\n",
    "df = df_all[['地区', '指标名', '2011', '2013',  '2015', '2017']].copy().set_index(['地区','指标名'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 😸 🤠 😺   深呼吸  😺 🤠 😸\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 题三（20 分）🌶 🌶 中"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据重塑reshape\n",
    "机考大家都会的\n",
    "* df.columns\n",
    "* [根据索引级别分组](https://nbviewer.jupyter.org/github/LearnXu/pydata-notebook/blob/master/Chapter-10/10.1%20GroupBy%20Mechanics%EF%BC%88%E5%88%86%E7%BB%84%E6%9C%BA%E5%88%B6%EF%BC%89.ipynb)\n",
    "   * set_index()\n",
    "   * reset_index()\n",
    "* [stack和unstack的形象理解](https://blog.csdn.net/anshuai_aw1/article/details/82830916)\n",
    "   * stack()\n",
    "   * unstack()\n",
    "\n",
    "所以，根据**题二的数据**，产出一数据框**dfs**\n",
    "1. 索引(index)需要设为['地区', '指标名', '年'] \n",
    "2. 数据值的栏位/行索引(column)需为'值' \n",
    "\n",
    "* 答案提示（前几行像）：\n",
    "![003](dataPANDAS/003.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>指标名</th>\n",
       "      <th>年</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"30\" valign=\"top\">110000</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">建筑业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>52455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>68501.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>82251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>99718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>116755.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>136599.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>159486.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>183183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">金融业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>172621.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>206110.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>248320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>253637.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>97658.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>113206.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>132339.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>150611.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>29934.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>40942.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>45909.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>53403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>55164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>73626.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>90724.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>124409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">金融业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>36619.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>61099.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>111921.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>144952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>37211.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>55689.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"30\" valign=\"top\">650000</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">建筑业城镇单位就业人员平均工资</th>\n",
       "      <th>2015</th>\n",
       "      <td>56238.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>59844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>48034.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>64214.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>78238.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>98251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">金融业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>59732.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>74551.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>88212.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>95374.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>45501.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>60859.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>75478.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>85516.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>29929.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>41001.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>45672.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>47699.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>20089.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>31279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>37964.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>42508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">金融业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>31007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>36275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>44239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>46858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <th>2011</th>\n",
       "      <td>25645.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>32319.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>43162.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>44347.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>976 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  值\n",
       "地区     指标名                           年             \n",
       "110000 建筑业城镇单位就业人员平均工资               2011   52455.0\n",
       "                                     2013   68501.0\n",
       "                                     2015   82251.0\n",
       "                                     2017   99718.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资    2011  116755.0\n",
       "                                     2013  136599.0\n",
       "                                     2015  159486.0\n",
       "                                     2017  183183.0\n",
       "       金融业城镇单位就业人员平均工资               2011  172621.0\n",
       "                                     2013  206110.0\n",
       "                                     2015  248320.0\n",
       "                                     2017  253637.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资   2011   97658.0\n",
       "                                     2013  113206.0\n",
       "                                     2015  132339.0\n",
       "                                     2017  150611.0\n",
       "       建筑业城镇私营单位就业人员平均工资             2011   29934.0\n",
       "                                     2013   40942.0\n",
       "                                     2015   45909.0\n",
       "                                     2017   53403.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资  2011   55164.0\n",
       "                                     2013   73626.0\n",
       "                                     2015   90724.0\n",
       "                                     2017  124409.0\n",
       "       金融业城镇私营单位就业人员平均工资             2011   36619.0\n",
       "                                     2013   61099.0\n",
       "                                     2015  111921.0\n",
       "                                     2017  144952.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资 2011   37211.0\n",
       "                                     2013   55689.0\n",
       "...                                             ...\n",
       "650000 建筑业城镇单位就业人员平均工资               2015   56238.0\n",
       "                                     2017   59844.0\n",
       "       信息传输、计算机服务和软件业城镇单位就业人员平均工资    2011   48034.0\n",
       "                                     2013   64214.0\n",
       "                                     2015   78238.0\n",
       "                                     2017   98251.0\n",
       "       金融业城镇单位就业人员平均工资               2011   59732.0\n",
       "                                     2013   74551.0\n",
       "                                     2015   88212.0\n",
       "                                     2017   95374.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资   2011   45501.0\n",
       "                                     2013   60859.0\n",
       "                                     2015   75478.0\n",
       "                                     2017   85516.0\n",
       "       建筑业城镇私营单位就业人员平均工资             2011   29929.0\n",
       "                                     2013   41001.0\n",
       "                                     2015   45672.0\n",
       "                                     2017   47699.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资  2011   20089.0\n",
       "                                     2013   31279.0\n",
       "                                     2015   37964.0\n",
       "                                     2017   42508.0\n",
       "       金融业城镇私营单位就业人员平均工资             2011   31007.0\n",
       "                                     2013   36275.0\n",
       "                                     2015   44239.0\n",
       "                                     2017   46858.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资 2011   25645.0\n",
       "                                     2013   32319.0\n",
       "                                     2015   43162.0\n",
       "                                     2017   44347.0\n",
       "\n",
       "[976 rows x 1 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 003\n",
    "\n",
    "dfstack = df.stack().reset_index()\n",
    "dfstack.columns = ['地区', '指标名', '年', '值']\n",
    "dfs = dfstack.copy().set_index(['地区', '指标名', '年'])\n",
    "dfs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 👽👼🤶  你可以的  🤶👼👽\n",
    "## 题四（20 分）🌶🌶🌶 稍难"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 根据**题三的数据**，产出一数据框**dff**\n",
    "1. 索引(index)需要设为['地区', '指标名']\n",
    "2. 数据值的栏位/行索引(column)需为   \n",
    "   ```MultiIndex(levels=[['值'], ['2011', '2013', '2015', '2017']],labels=[[0, 0, 0, 0], [0, 1, 2, 3]],names=[None, '年'])``\n",
    "3. 此数据框需为新的副本，使用.copy()`\n",
    "\n",
    "* 答案提示（前几行像）：\n",
    "![004](dataPANDAS/004.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>年</th>\n",
       "      <th>2011</th>\n",
       "      <th>2013</th>\n",
       "      <th>2015</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>指标名</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">110000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>116755.0</td>\n",
       "      <td>136599.0</td>\n",
       "      <td>159486.0</td>\n",
       "      <td>183183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>55164.0</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>90724.0</td>\n",
       "      <td>124409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>52455.0</td>\n",
       "      <td>68501.0</td>\n",
       "      <td>82251.0</td>\n",
       "      <td>99718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29934.0</td>\n",
       "      <td>40942.0</td>\n",
       "      <td>45909.0</td>\n",
       "      <td>53403.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>97658.0</td>\n",
       "      <td>113206.0</td>\n",
       "      <td>132339.0</td>\n",
       "      <td>150611.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>37211.0</td>\n",
       "      <td>55689.0</td>\n",
       "      <td>66597.0</td>\n",
       "      <td>79731.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>172621.0</td>\n",
       "      <td>206110.0</td>\n",
       "      <td>248320.0</td>\n",
       "      <td>253637.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>36619.0</td>\n",
       "      <td>61099.0</td>\n",
       "      <td>111921.0</td>\n",
       "      <td>144952.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">120000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>74804.0</td>\n",
       "      <td>102922.0</td>\n",
       "      <td>134331.0</td>\n",
       "      <td>151778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>41082.0</td>\n",
       "      <td>46580.0</td>\n",
       "      <td>79928.0</td>\n",
       "      <td>95320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>43161.0</td>\n",
       "      <td>54095.0</td>\n",
       "      <td>64141.0</td>\n",
       "      <td>70649.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29177.0</td>\n",
       "      <td>39704.0</td>\n",
       "      <td>46771.0</td>\n",
       "      <td>49933.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>91848.0</td>\n",
       "      <td>102610.0</td>\n",
       "      <td>123312.0</td>\n",
       "      <td>137969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29137.0</td>\n",
       "      <td>43931.0</td>\n",
       "      <td>60946.0</td>\n",
       "      <td>69255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>97006.0</td>\n",
       "      <td>118448.0</td>\n",
       "      <td>112059.0</td>\n",
       "      <td>113813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>31071.0</td>\n",
       "      <td>43257.0</td>\n",
       "      <td>57510.0</td>\n",
       "      <td>81116.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">130000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>46842.0</td>\n",
       "      <td>69718.0</td>\n",
       "      <td>93983.0</td>\n",
       "      <td>84317.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18638.0</td>\n",
       "      <td>27827.0</td>\n",
       "      <td>34832.0</td>\n",
       "      <td>42182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>27425.0</td>\n",
       "      <td>34670.0</td>\n",
       "      <td>39182.0</td>\n",
       "      <td>51771.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22670.0</td>\n",
       "      <td>28852.0</td>\n",
       "      <td>33813.0</td>\n",
       "      <td>38191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>59318.0</td>\n",
       "      <td>61114.0</td>\n",
       "      <td>69744.0</td>\n",
       "      <td>81947.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22348.0</td>\n",
       "      <td>31978.0</td>\n",
       "      <td>37431.0</td>\n",
       "      <td>42280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>53190.0</td>\n",
       "      <td>65547.0</td>\n",
       "      <td>74795.0</td>\n",
       "      <td>77845.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21833.0</td>\n",
       "      <td>29054.0</td>\n",
       "      <td>34564.0</td>\n",
       "      <td>41474.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">140000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>35697.0</td>\n",
       "      <td>49299.0</td>\n",
       "      <td>60160.0</td>\n",
       "      <td>77870.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>17068.0</td>\n",
       "      <td>21177.0</td>\n",
       "      <td>33029.0</td>\n",
       "      <td>30677.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>30399.0</td>\n",
       "      <td>36719.0</td>\n",
       "      <td>42494.0</td>\n",
       "      <td>50384.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21658.0</td>\n",
       "      <td>29185.0</td>\n",
       "      <td>35486.0</td>\n",
       "      <td>35768.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>37549.0</td>\n",
       "      <td>50793.0</td>\n",
       "      <td>58390.0</td>\n",
       "      <td>67918.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>16919.0</td>\n",
       "      <td>26056.0</td>\n",
       "      <td>31594.0</td>\n",
       "      <td>31175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">620000</th>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>25334.0</td>\n",
       "      <td>34793.0</td>\n",
       "      <td>40551.0</td>\n",
       "      <td>47590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18834.0</td>\n",
       "      <td>25256.0</td>\n",
       "      <td>30687.0</td>\n",
       "      <td>36270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>34475.0</td>\n",
       "      <td>49957.0</td>\n",
       "      <td>67352.0</td>\n",
       "      <td>85197.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>19531.0</td>\n",
       "      <td>25220.0</td>\n",
       "      <td>35573.0</td>\n",
       "      <td>51435.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>38163.0</td>\n",
       "      <td>48791.0</td>\n",
       "      <td>59923.0</td>\n",
       "      <td>63050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18577.0</td>\n",
       "      <td>21144.0</td>\n",
       "      <td>29668.0</td>\n",
       "      <td>39157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">630000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>47356.0</td>\n",
       "      <td>49989.0</td>\n",
       "      <td>72527.0</td>\n",
       "      <td>79183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>17376.0</td>\n",
       "      <td>24681.0</td>\n",
       "      <td>30810.0</td>\n",
       "      <td>35235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>32352.0</td>\n",
       "      <td>40242.0</td>\n",
       "      <td>48717.0</td>\n",
       "      <td>57674.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>21598.0</td>\n",
       "      <td>24730.0</td>\n",
       "      <td>36597.0</td>\n",
       "      <td>38588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>55466.0</td>\n",
       "      <td>59097.0</td>\n",
       "      <td>69678.0</td>\n",
       "      <td>87248.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>22629.0</td>\n",
       "      <td>40967.0</td>\n",
       "      <td>44716.0</td>\n",
       "      <td>52103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>54446.0</td>\n",
       "      <td>67182.0</td>\n",
       "      <td>81359.0</td>\n",
       "      <td>98911.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>18112.0</td>\n",
       "      <td>19408.0</td>\n",
       "      <td>26221.0</td>\n",
       "      <td>28776.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">640000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>52173.0</td>\n",
       "      <td>65043.0</td>\n",
       "      <td>77487.0</td>\n",
       "      <td>90085.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>16305.0</td>\n",
       "      <td>29269.0</td>\n",
       "      <td>32637.0</td>\n",
       "      <td>39889.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>29065.0</td>\n",
       "      <td>39589.0</td>\n",
       "      <td>44283.0</td>\n",
       "      <td>47483.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>23403.0</td>\n",
       "      <td>36178.0</td>\n",
       "      <td>39785.0</td>\n",
       "      <td>42756.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>46198.0</td>\n",
       "      <td>57000.0</td>\n",
       "      <td>71581.0</td>\n",
       "      <td>78182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25655.0</td>\n",
       "      <td>38531.0</td>\n",
       "      <td>45598.0</td>\n",
       "      <td>46887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>62587.0</td>\n",
       "      <td>74026.0</td>\n",
       "      <td>81019.0</td>\n",
       "      <td>85861.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25575.0</td>\n",
       "      <td>30540.0</td>\n",
       "      <td>40028.0</td>\n",
       "      <td>38284.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">650000</th>\n",
       "      <th>信息传输、计算机服务和软件业城镇单位就业人员平均工资</th>\n",
       "      <td>48034.0</td>\n",
       "      <td>64214.0</td>\n",
       "      <td>78238.0</td>\n",
       "      <td>98251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业城镇私营单位就业人员平均工资</th>\n",
       "      <td>20089.0</td>\n",
       "      <td>31279.0</td>\n",
       "      <td>37964.0</td>\n",
       "      <td>42508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇单位就业人员平均工资</th>\n",
       "      <td>35558.0</td>\n",
       "      <td>45940.0</td>\n",
       "      <td>56238.0</td>\n",
       "      <td>59844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>建筑业城镇私营单位就业人员平均工资</th>\n",
       "      <td>29929.0</td>\n",
       "      <td>41001.0</td>\n",
       "      <td>45672.0</td>\n",
       "      <td>47699.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇单位就业人员平均工资</th>\n",
       "      <td>45501.0</td>\n",
       "      <td>60859.0</td>\n",
       "      <td>75478.0</td>\n",
       "      <td>85516.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资</th>\n",
       "      <td>25645.0</td>\n",
       "      <td>32319.0</td>\n",
       "      <td>43162.0</td>\n",
       "      <td>44347.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇单位就业人员平均工资</th>\n",
       "      <td>59732.0</td>\n",
       "      <td>74551.0</td>\n",
       "      <td>88212.0</td>\n",
       "      <td>95374.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业城镇私营单位就业人员平均工资</th>\n",
       "      <td>31007.0</td>\n",
       "      <td>36275.0</td>\n",
       "      <td>44239.0</td>\n",
       "      <td>46858.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>244 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             值                              \n",
       "年                                         2011      2013      2015      2017\n",
       "地区     指标名                                                                  \n",
       "110000 信息传输、计算机服务和软件业城镇单位就业人员平均工资     116755.0  136599.0  159486.0  183183.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    55164.0   73626.0   90724.0  124409.0\n",
       "       建筑业城镇单位就业人员平均工资                 52455.0   68501.0   82251.0   99718.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29934.0   40942.0   45909.0   53403.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     97658.0  113206.0  132339.0  150611.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   37211.0   55689.0   66597.0   79731.0\n",
       "       金融业城镇单位就业人员平均工资                172621.0  206110.0  248320.0  253637.0\n",
       "       金融业城镇私营单位就业人员平均工资               36619.0   61099.0  111921.0  144952.0\n",
       "120000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      74804.0  102922.0  134331.0  151778.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    41082.0   46580.0   79928.0   95320.0\n",
       "       建筑业城镇单位就业人员平均工资                 43161.0   54095.0   64141.0   70649.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29177.0   39704.0   46771.0   49933.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     91848.0  102610.0  123312.0  137969.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   29137.0   43931.0   60946.0   69255.0\n",
       "       金融业城镇单位就业人员平均工资                 97006.0  118448.0  112059.0  113813.0\n",
       "       金融业城镇私营单位就业人员平均工资               31071.0   43257.0   57510.0   81116.0\n",
       "130000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      46842.0   69718.0   93983.0   84317.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    18638.0   27827.0   34832.0   42182.0\n",
       "       建筑业城镇单位就业人员平均工资                 27425.0   34670.0   39182.0   51771.0\n",
       "       建筑业城镇私营单位就业人员平均工资               22670.0   28852.0   33813.0   38191.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     59318.0   61114.0   69744.0   81947.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   22348.0   31978.0   37431.0   42280.0\n",
       "       金融业城镇单位就业人员平均工资                 53190.0   65547.0   74795.0   77845.0\n",
       "       金融业城镇私营单位就业人员平均工资               21833.0   29054.0   34564.0   41474.0\n",
       "140000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      35697.0   49299.0   60160.0   77870.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    17068.0   21177.0   33029.0   30677.0\n",
       "       建筑业城镇单位就业人员平均工资                 30399.0   36719.0   42494.0   50384.0\n",
       "       建筑业城镇私营单位就业人员平均工资               21658.0   29185.0   35486.0   35768.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     37549.0   50793.0   58390.0   67918.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   16919.0   26056.0   31594.0   31175.0\n",
       "...                                        ...       ...       ...       ...\n",
       "620000 建筑业城镇单位就业人员平均工资                 25334.0   34793.0   40551.0   47590.0\n",
       "       建筑业城镇私营单位就业人员平均工资               18834.0   25256.0   30687.0   36270.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     34475.0   49957.0   67352.0   85197.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   19531.0   25220.0   35573.0   51435.0\n",
       "       金融业城镇单位就业人员平均工资                 38163.0   48791.0   59923.0   63050.0\n",
       "       金融业城镇私营单位就业人员平均工资               18577.0   21144.0   29668.0   39157.0\n",
       "630000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      47356.0   49989.0   72527.0   79183.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    17376.0   24681.0   30810.0   35235.0\n",
       "       建筑业城镇单位就业人员平均工资                 32352.0   40242.0   48717.0   57674.0\n",
       "       建筑业城镇私营单位就业人员平均工资               21598.0   24730.0   36597.0   38588.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     55466.0   59097.0   69678.0   87248.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   22629.0   40967.0   44716.0   52103.0\n",
       "       金融业城镇单位就业人员平均工资                 54446.0   67182.0   81359.0   98911.0\n",
       "       金融业城镇私营单位就业人员平均工资               18112.0   19408.0   26221.0   28776.0\n",
       "640000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      52173.0   65043.0   77487.0   90085.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    16305.0   29269.0   32637.0   39889.0\n",
       "       建筑业城镇单位就业人员平均工资                 29065.0   39589.0   44283.0   47483.0\n",
       "       建筑业城镇私营单位就业人员平均工资               23403.0   36178.0   39785.0   42756.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     46198.0   57000.0   71581.0   78182.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   25655.0   38531.0   45598.0   46887.0\n",
       "       金融业城镇单位就业人员平均工资                 62587.0   74026.0   81019.0   85861.0\n",
       "       金融业城镇私营单位就业人员平均工资               25575.0   30540.0   40028.0   38284.0\n",
       "650000 信息传输、计算机服务和软件业城镇单位就业人员平均工资      48034.0   64214.0   78238.0   98251.0\n",
       "       信息传输、计算机服务和软件业城镇私营单位就业人员平均工资    20089.0   31279.0   37964.0   42508.0\n",
       "       建筑业城镇单位就业人员平均工资                 35558.0   45940.0   56238.0   59844.0\n",
       "       建筑业城镇私营单位就业人员平均工资               29929.0   41001.0   45672.0   47699.0\n",
       "       科学研究、技术服务和地质勘查业城镇单位就业人员平均工资     45501.0   60859.0   75478.0   85516.0\n",
       "       科学研究、技术服务和地质勘查业城镇私营单位就业人员平均工资   25645.0   32319.0   43162.0   44347.0\n",
       "       金融业城镇单位就业人员平均工资                 59732.0   74551.0   88212.0   95374.0\n",
       "       金融业城镇私营单位就业人员平均工资               31007.0   36275.0   44239.0   46858.0\n",
       "\n",
       "[244 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 004\n",
    "dff = dfs.unstack(2)\n",
    "dff\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 😫😱😥 快了 😥😱😫\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 题五（20 分）🌶 🌶 中"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 根据**题四的数据**，产出一字典 **gd_mean** \n",
    "  *.  取‘2017’年全国所有省份的指标值取平均值 \n",
    "* 答案提示\n",
    "  1. 可使用.mean()\n",
    "  2. 可使用 产出一数据列 Series (pandas.core.series.Series) ，再转换成字典\n",
    "  3. 答案含有：```{'信息传输、计算机服务和软件业城镇单位就业人员平均工资': 103597.90322580645,'信息传输、计算机服务和软件业城镇私营单位就业人员平均工资': 54032.433333333334,'建筑业城镇单位就业人员平均工资': 55400.0, .....,``"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "''",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-723ab28dc4d6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 005\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mgd_mean_SS\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdff\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m''\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmean\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mgd_mean_SS\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgd_mean_SS\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdroplevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mgd_mean\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mgd_mean_SS\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'2017'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_dict\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mgd_mean\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mgroupby\u001b[1;34m(self, by, axis, level, as_index, sort, group_keys, squeeze, observed, **kwargs)\u001b[0m\n\u001b[0;32m   6657\u001b[0m         return groupby(self, by=by, axis=axis, level=level, as_index=as_index,\n\u001b[0;32m   6658\u001b[0m                        \u001b[0msort\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msort\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgroup_keys\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mgroup_keys\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msqueeze\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msqueeze\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 6659\u001b[1;33m                        observed=observed, **kwargs)\n\u001b[0m\u001b[0;32m   6660\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6661\u001b[0m     def asfreq(self, freq, method=None, how=None, normalize=False,\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mgroupby\u001b[1;34m(obj, by, **kwds)\u001b[0m\n\u001b[0;32m   2150\u001b[0m         \u001b[1;32mraise\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'invalid type: %s'\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2151\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2152\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mklass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mby\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2153\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2154\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, squeeze, observed, **kwargs)\u001b[0m\n\u001b[0;32m    597\u001b[0m                                                     \u001b[0msort\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msort\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    598\u001b[0m                                                     \u001b[0mobserved\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mobserved\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 599\u001b[1;33m                                                     mutated=self.mutated)\n\u001b[0m\u001b[0;32m    600\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    601\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mobj\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mobj\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m_get_grouper\u001b[1;34m(obj, key, axis, level, sort, observed, mutated, validate)\u001b[0m\n\u001b[0;32m   3289\u001b[0m                 \u001b[0min_axis\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgpr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgpr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3290\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3291\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgpr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3292\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgpr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mGrouper\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mgpr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkey\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3293\u001b[0m             \u001b[1;31m# Add key to exclusions\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: ''"
     ]
    }
   ],
   "source": [
    "# 005\n",
    "gd_mean_SS = dff.groupby(by='').mean()\n",
    "gd_mean_SS.columns = gd_mean_SS.columns.droplevel()\n",
    "gd_mean= gd_mean_SS.loc['2017'].to_dict()\n",
    "gd_mean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 🙌🙌🙌🎈 👍 恭喜 👍 🎉🙌🙌🙌\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 🏁🏁🏁回报答题分数（仅供参考）🏁🏁🏁"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name '_which_' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-7-96b0bd90a830>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[0mPandasCourse\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mPC\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mscore_details\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mPC\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mscore_answers\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlocals\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0m_which_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mscore_details\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name '_which_' is not defined"
     ]
    }
   ],
   "source": [
    "#终001 （勿改动，执行即可）回报答题分数\n",
    "import PandasCourse as PC\n",
    "\n",
    "score_details = PC.score_answers(locals(), _which_)\n",
    "print (score_details[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "nbTranslate": {
   "displayLangs": [
    "*"
   ],
   "hotkey": "alt-t",
   "langInMainMenu": true,
   "sourceLang": "en",
   "targetLang": "fr",
   "useGoogleTranslate": true
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
